import pymysql,json
from DBUtils.PooledDB import PooledDB
from Web.Service import XssService
from Web.Tools import Tool

pool = PooledDB(pymysql,5,host='krc21n.nidaili.net',user='root',passwd='root',db='first',port=11168) #5为连接


'''
简易mysql操作类

'''
#sql 输入需要预编译语句
def execute(sql,PojoClass=None):
    cnn = pool.connection( )
    cursor = cnn.cursor( )
    try:
        code = cursor.execute( sql )
    except Exception as err:
        print( "[error] sql can not be understand , check format   \n",err )
        return []
    cursor.connection.commit( )

    r = cursor.fetchall( )
    #分析命令
    comandtype = Tool.text_getMiddle( sql.lower( ) , "" , " " )
    # 只有select 需要对象返回  其他只要返回被影响的行数
    if(comandtype[0]!="select"):
        return code

    if(len(comandtype)!=0):
        midstr = Tool.text_getMiddle( sql.lower( ) , comandtype[0 ] , "from" )
    #输入的字段数组 如select id,name from ... 则 keyList=['id','name']
    KeyList = [ ]
    if (len(midstr)!=0):
        KeyList = midstr[0].replace( " " , "" ).split(",")

    aList = [ ]
    for row in r:
        instance=PojoClass()
        u=0
        #items为输入的结构体的成员   与   输入的字段数组 keyList=['id','name'] 进行比较 赋值指定
        print(KeyList)
        print(row)
        for name , value in vars( instance ).items( ):
            #根据结果填充结构体
            if (len(KeyList)>0  and( KeyList[0]=="*" or (u<=len(KeyList) and name==KeyList[u]))):
                instance.__setattr__(name,defXSS(row[u]))
                print("SET",name,defXSS(row[u]))
                u=u+1
        #记录形成list
        aList.append( instance )
    cursor.close()
    cnn.close()

    return aList


#sql='insert into tb1(name,age,phone) values({0},{1},{2})'.format("7",54,"1566456465")
def insert(tbname,instance):
    nameList = []
    valueList = []
    preList = []
    str=""
    for name , value in vars( instance ).items( ):
        if None!=getattr(instance,name):
            nameList.append(name)
            valueList.append( getattr(instance,name) )
    print(nameList)
    print( valueList )
    str+=" ('"
    for name in nameList:
        str += name+"','"
    str=str.strip("','")+"') VALUES ('"
    for value in valueList:
        str += value+"','"

    mysqlTool.execute( "insert into " + tbname + sql)
'''
def toInsFormat( Dict ):
    name = ""
    value = ""
    prelist=""
    for dic in Dict.keys( ):
        name = name + dic + ","
    for dic in Dict.values( ):
        value = value + "'"+dic + "',"
    for count in range(0,len(Dict)):
        prelist = "{"+str(count)+"},"
    ret = " (" + name + ") VALUES (" + value + ")"
    return ret.replace( ",)" , ")" ).replace('\n','<br>')
'''
def insert(tbname,instance):
    cnn = pool.connection( )
    cursor = cnn.cursor( )

    nameStr=""
    preStr=""
    valueList=[]
    #字符串拼接
    for name , value in vars( instance ).items( ):
        nameStr+=name+","
        preStr+="%s,"
        valueList.append(value)
    #print("insert into " + tbname +" ( "+nameStr[:-1]+" ) values ("+preStr[:-1]+")")
    rt=cursor.execute("insert into " + tbname +" ( "+nameStr[:-1]+" ) values ("+preStr[:-1]+")",valueList)
    cursor.connection.commit( )
    return rt

def insert2(tbname,instance,requestinstance):
    cnn = pool.connection( )
    cursor = cnn.cursor( )

    #post值赋予对象中
    for key in requestinstance.keys( ):
        if hasattr(instance,key):
            setattr(instance,key,requestinstance[key])

    nameStr=""
    preStr=""
    valueList=[]
    #字符串拼接
    for name , value in vars( instance ).items( ):
        nameStr+=name+","
        preStr+="%s,"
        valueList.append(value)
    #print("insert into " + tbname +" ( "+nameStr[:-1]+" ) values ("+preStr[:-1]+")",valueList)
    rt=cursor.execute("insert into " + tbname +" ( "+nameStr[:-1]+" ) values ("+preStr[:-1]+")",valueList)
    cursor.connection.commit( )
    return rt

def select(tbname,instance):
    cnn = pool.connection( )
    cursor = cnn.cursor( )

    nameStr = ""
    preStr = ""
    valueList = [ ]
    # 字符串拼接
    for name , value in vars( instance ).items( ):
        nameStr += name + ","
        preStr += "%s,"
        valueList.append( value )
    # print("insert into " + tbname +" ( "+nameStr[:-1]+" ) values ("+preStr[:-1]+")")
    rt = cursor.execute( "select * from " + tbname + " ( " + nameStr[ :-1 ] + " ) values (" + preStr[ :-1 ] + ")" ,
                         valueList )
    cursor.connection.commit( )
    return rt

def select2(tbname,instance,requestinstance):
    cnn = pool.connection( )
    cursor = cnn.cursor( )

    # post值赋予对象中
    for key in requestinstance.keys( ):
        if hasattr( instance , key ):
            setattr( instance , key , requestinstance[ key ] )

    nameStr = ""
    preStr = ""
    valueList = [ ]
    # 字符串拼接
    for name , value in vars( instance ).items( ):
        nameStr += name + ","
        preStr += "%s,"
        valueList.append( value )
    # print("insert into " + tbname +" ( "+nameStr[:-1]+" ) values ("+preStr[:-1]+")",valueList)
    rt = cursor.execute( "insert into " + tbname + " ( " + nameStr[ :-1 ] + " ) values (" + preStr[ :-1 ] + ")" ,
                         valueList )
    cursor.connection.commit( )
    return rt

def toJson(list):
    return json.dumps( list , default=lambda o: o.__dict__ , sort_keys=True , indent=4 )


#xss漏洞众多 禁止所有标签为上策 必须用标签的话 使用开源的xss过滤库
def defXSS(content):
    parser = XssService.XssHtml( )
    parser.feed(str(content))
    parser.close()
    #print(parser.getHtml())
    return parser.getHtml()


if __name__ == '__main__':
    '''
    aList = mysqlTool.execute("select name from db where id =2",User)
    print("----")
    print( aList )
    if(len(aList)!=0):
    print(aList[0].name)
    '''
    # code = mysqlTool.execute("update db set name='3' where id =2")
    # code = mysqlTool.execute("delete from db where name =1")
    #code = execute("insert into db values (3,3)")
    #print(code)
    #toInsFormat()
